/********************************************************************************
 * SearchServiceDelegator.java
 *
 * Created by Jigar Mistry
 * Last modified Date: Jan 21, 2009 5:34:14 PM by Jigar Mistry
 * Version : 0.1
 * Copyright (c) 2008 - 2009 cyberThink Infotech Pvt. Ltd., All rights reserved.
 ********************************************************************************/
package com.printeverywhere.delegator;

import java.math.BigInteger;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

import com.printeverywhere.dto.BrowseItems;
import com.printeverywhere.dto.Items;
import com.printeverywhere.util.HibernateUtil;

public class SearchServiceDelegator {
	/** The log. */
	private static Logger log = Logger.getLogger(ReserveAuctionListingFeesServiceDelegator.class);

	public static int getItemCountFor(int type, int category, int location, String language) {
		if(log.isInfoEnabled()){
			log.info("Getting list for getItemCountFor without search criteria".intern());
		}
		Session session = HibernateUtil.getSession();
		try {
			Transaction transaction = session.beginTransaction();
			Criteria criteria = session.createCriteria(Items.class);

			if(type != 0)
				criteria.add(Restrictions.eq("itemtype.id", type));
			if(category != 0)
				criteria.add(Restrictions.eq("generalCategories.categoryId", category));
			if(location != 0) {
				criteria.add(Restrictions.or(Restrictions.or(Restrictions.eq("locationCategoriesByRegion.categoryId", location), Restrictions.eq("locationCategoriesByCountry.categoryId", location)), Restrictions.eq("locationCategoriesByState.categoryId", location)));
			}
			if(!language.equals("0"))
				criteria.add(Restrictions.eq("itemLanguage.languageCode", language));

			criteria.setProjection(Projections.rowCount());
			transaction.commit();
			return (Integer)criteria.uniqueResult();
		} catch (HibernateException e) {
			log.error("Got exception when getting list for getItemCountFor without search criteria".intern());
			throw e;
		} finally {
			if(log.isInfoEnabled()){
				log.info("Closing session".intern());
			}
			session.flush();
			session.close();
		}
	}

	public static int getItemCountForParent(int type, int category, int location, String language) {
		if(log.isInfoEnabled()){
			log.info("Getting list for getItemCountFor without search criteria".intern());
		}
		Session session = HibernateUtil.getSession();
		try {
			boolean flag = false;
			String typeCondition = "";
			if(type != 0) {
				typeCondition = "type = " + type;
				flag = true;
			}

			String langCondition = "";
			if(!language.equals("0")) {
				langCondition = ((flag) ? " AND " : "")  + "itemLanguage like '" + language + "'";
				flag = true;
			}

			String catCondition = "";
			if(category != 0) {
				catCondition = ((flag) ? " AND " : "")  + "itemId IN (SELECT itemId FROM item_categories WHERE categoryId IN (SELECT categoryId FROM general_categories WHERE mainparentId = (SELECT mainparentId FROM general_categories WHERE categoryId = " + category + ") AND categoryId >= " + category + "))";
				flag = true;
			}

			String locConition = "";
			if(location != 0) {
				locConition = ((flag) ? " AND " : "")  + "(region IN (SELECT categoryId FROM location_categories WHERE mainparentId = (SELECT mainparentId FROM location_categories WHERE categoryId = " + location + ") AND categoryId >= " + location + ") OR country IN (SELECT categoryId FROM location_categories WHERE mainparentId = (SELECT mainparentId FROM location_categories WHERE categoryId = " + location + ") AND categoryId >= " + location + ") OR state IN(SELECT categoryId FROM location_categories WHERE mainparentId = (SELECT mainparentId FROM location_categories WHERE categoryId = " + location + ") AND categoryId >= " + location + "))";
				flag = true;
			}

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT count(itemId) FROM items");
			if(flag) {
				sql.append(" WHERE ").append(typeCondition).append(langCondition).append(catCondition).append(locConition).append(";");
			} else {
				sql.append(";");
			}

			Query query = session.createSQLQuery(sql.toString());
			List count = query.list();

			if(count != null && count.size() > 0) {
				BigInteger intValue = (BigInteger) count.get(0);
				return intValue.intValue();
			} else
				return 0;
		} catch (HibernateException e) {
			log.error("Got exception when getting list for getItemCountFor without search criteria".intern());
			throw e;
		} finally {
			if(log.isInfoEnabled()){
				log.info("Closing session".intern());
			}
			session.flush();
			session.close();
		}
	}

	public static void main(String[] args) {
		SearchServiceDelegator.getItemCountForParent(0, 15, 0, "0");
	}
}
